# coding=utf-8
import arcpy
import os
import sys

# 设置默认编码为 UTF-8
reload(sys)
sys.setdefaultencoding('utf-8')


def get_state_value(feature_class, object_id, field_name="STATE"):
    """查询要素类中指定OLD_OBJECTID的指定字段值"""
    field_value = None
    fields = [field_name]
    where_clause = "OLD_OBJECTID = {}".format(object_id)

    try:
        with arcpy.da.SearchCursor(feature_class, fields, where_clause) as cursor:
            for row in cursor:
                field_value = row[0]
                break  # 找到即退出循环
    except Exception as e:
        print("查询字段 %s 时出错: %s" % (field_name, str(e)))

    return field_value


def calculate_relative_error(value_2019, value_2024, threshold=0.1):
    """计算相对误差并返回相似度结果（0-1）或特殊标记（5）"""
    if value_2019 == 0 or value_2024 == 0:
        return 5  # 任意值为0时返回5，用于标记特殊状态

    # 计算相对误差（绝对值）
    relative_error = abs(value_2024 - value_2019) / max(abs(value_2019), abs(value_2024))
    # 转换为相似度（0-1，误差越小相似度越高）
    similarity = 1 - relative_error
    return similarity


try:
    # 自定义路径和字段名（可根据需求修改）
    mdb_2019 = r"D:\ArcGis\DATA\19_test.mdb"
    mdb_2024 = r"D:\ArcGis\DATA\24_test.mdb"
    add_output_gdb = r"D:\ArcGis\Output\add_data.mdb"  # 新增数据输出路径
    no_output_gdb = r"D:\ArcGis\Output\no_data.mdb"  # 消失数据输出路径
    output_gdb = r"D:\ArcGis\Output\output_test.mdb"  # 原输出路径（保留）

    # 可自定义的对比字段
    field_a = "KFQKMC"  # 对比字段A
    field_b = "Shape_Length"  # 对比字段B
    similarity_threshold = 0.9  # 相似度阈值d，可自定义（0-1）

    # 创建输出地理数据库（如果不存在）
    for gdb in [add_output_gdb, no_output_gdb, output_gdb]:
        if not arcpy.Exists(gdb):
            arcpy.CreateFileGDB_management(os.path.dirname(gdb), os.path.basename(gdb))

    # 确保地理处理环境设置
    arcpy.env.overwriteOutput = True

    # ============== 处理2019年数据：添加必要字段 ==============
    arcpy.env.workspace = mdb_2019
    feature_classes_2019 = {}
    for fc in arcpy.ListFeatureClasses(feature_type="Polygon"):
        fc_path = os.path.join(mdb_2019, fc)
        feature_classes_2019[fc] = fc_path

        # 检查并添加必要字段
        fields = [f.name.upper() for f in arcpy.ListFields(fc_path)]
        if "OLD_OBJECTID" not in fields:
            print("为2019年要素类 %s 添加OLD_OBJECTID字段" % fc)
            arcpy.AddField_management(fc_path, "OLD_OBJECTID", "LONG")
            with arcpy.da.UpdateCursor(fc_path, ["OBJECTID", "OLD_OBJECTID"]) as cursor:
                for row in cursor:
                    row[1] = row[0]
                    cursor.updateRow(row)

        if "STATE" not in fields:
            print("为2019年要素类 %s 添加STATE字段" % fc)
            arcpy.AddField_management(fc_path, "STATE", "LONG")
            with arcpy.da.UpdateCursor(fc_path, ["STATE"]) as cursor:
                for row in cursor:
                    row[0] = 1  # 初始化为1（待判断）
                    cursor.updateRow(row)

    # ============== 处理2024年数据：添加必要字段 ==============
    arcpy.env.workspace = mdb_2024
    feature_classes_2024 = {}
    for fc in arcpy.ListFeatureClasses(feature_type="Polygon"):
        fc_path = os.path.join(mdb_2024, fc)
        feature_classes_2024[fc] = fc_path

        # 检查并添加必要字段
        fields = [f.name.upper() for f in arcpy.ListFields(fc_path)]
        if "OLD_OBJECTID" not in fields:
            print("为2024年要素类 %s 添加OLD_OBJECTID字段" % fc)
            arcpy.AddField_management(fc_path, "OLD_OBJECTID", "LONG")
            with arcpy.da.UpdateCursor(fc_path, ["OBJECTID", "OLD_OBJECTID"]) as cursor:
                for row in cursor:
                    row[1] = row[0]
                    cursor.updateRow(row)

        if "STATE" not in fields:
            print("为2024年要素类 %s 添加STATE字段" % fc)
            arcpy.AddField_management(fc_path, "STATE", "LONG")
            with arcpy.da.UpdateCursor(fc_path, ["STATE"]) as cursor:
                for row in cursor:
                    row[0] = 1  # 初始化为1（待判断）
                    cursor.updateRow(row)

    # ============== 第一次提取：处理STATE=2和STATE=3的数据 ==============
    print("\n===== 开始第一次提取 =====")
    common_fcs = set(feature_classes_2019.keys()) & set(feature_classes_2024.keys())

    if not common_fcs:
        print("两个数据库中没有找到同名的面要素类！")
    else:
        for fc in common_fcs:
            print("\n处理要素类: %s" % fc)

            # 获取2024年数据中STATE=2的记录（新增数据）
            new_ids_from_state2 = set()
            with arcpy.da.SearchCursor(feature_classes_2024[fc], ["OLD_OBJECTID"]) as cursor:
                for row in cursor:
                    old_objectid = row[0]
                    state = get_state_value(feature_classes_2024[fc], old_objectid)
                    if state == 2:
                        new_ids_from_state2.add(old_objectid)
            print("  从STATE=2识别的新增记录: %d 条" % len(new_ids_from_state2))

            # 获取2019年数据中STATE=3的记录（消失数据）
            deleted_ids_from_state3 = set()
            with arcpy.da.SearchCursor(feature_classes_2019[fc], ["OLD_OBJECTID"]) as cursor:
                for row in cursor:
                    old_objectid = row[0]
                    state = get_state_value(feature_classes_2019[fc], old_objectid)
                    if state == 3:
                        deleted_ids_from_state3.add(old_objectid)
            print("  从STATE=3识别的消失记录: %d 条" % len(deleted_ids_from_state3))

            # 导出新增数据到add_data.mdb（名称改为原名称加_add后缀）
            if new_ids_from_state2:
                new_fc_name = "%s_add" % fc
                new_fc_path = os.path.join(add_output_gdb, new_fc_name)
                sql_export_new = "OLD_OBJECTID IN (%s)" % ", ".join(map(str, new_ids_from_state2))
                arcpy.FeatureClassToFeatureClass_conversion(
                    in_features=feature_classes_2024[fc],
                    out_path=add_output_gdb,
                    out_name=new_fc_name,
                    where_clause=sql_export_new
                )
                print("  已导出新增记录到: %s\\%s" % (add_output_gdb, new_fc_name))

            # 导出消失数据到no_data.mdb（名称改为原名称加_no后缀）
            if deleted_ids_from_state3:
                deleted_fc_name = "%s_no" % fc
                deleted_fc_path = os.path.join(no_output_gdb, deleted_fc_name)
                sql_export_deleted = "OLD_OBJECTID IN (%s)" % ", ".join(map(str, deleted_ids_from_state3))
                arcpy.FeatureClassToFeatureClass_conversion(
                    in_features=feature_classes_2019[fc],
                    out_path=no_output_gdb,
                    out_name=deleted_fc_name,
                    where_clause=sql_export_deleted
                )
                print("  已导出消失记录到: %s\\%s" % (no_output_gdb, deleted_fc_name))

    # ============== 第二次提取：处理剩余STATE=1的数据 ==============
    print("\n===== 开始第二次提取 =====")
    for fc in common_fcs:
        print("\n处理要素类: %s" % fc)

        # 获取2024年所有STATE=1的数据（按OLD_OBJECTID升序排列）
        pending_2024_ids = []
        sql_2024_pending = "STATE = 1"
        fields_2024 = ["OLD_OBJECTID"]
        with arcpy.da.SearchCursor(feature_classes_2024[fc], fields_2024, sql_2024_pending,
                                   sql_clause=(None, "ORDER BY OLD_OBJECTID ASC")) as cursor:
            for row in cursor:
                pending_2024_ids.append(row[0])
        print("  2024年待处理记录(STATE=1): %d 条" % len(pending_2024_ids))

        # 获取2019年所有STATE=1的数据（按OLD_OBJECTID升序排列）
        pending_2019_ids = []
        sql_2019_pending = "STATE = 1"
        fields_2019 = ["OLD_OBJECTID", field_a, field_b]
        with arcpy.da.SearchCursor(feature_classes_2019[fc], fields_2019, sql_2019_pending,
                                   sql_clause=(None, "ORDER BY OLD_OBJECTID ASC")) as cursor:
            for row in cursor:
                pending_2019_ids.append({
                    "old_objectid": row[0],
                    field_a: row[1],
                    field_b: row[2]
                })
        print("  2019年待处理记录(STATE=1): %d 条" % len(pending_2019_ids))

        # 处理2024年数据（按OLD_OBJECTID升序）
        for old_objectid_2024 in pending_2024_ids:
            # 获取当前24年记录的字段值
            a_value_2024 = get_state_value(feature_classes_2024[fc], old_objectid_2024, field_a)
            b_value_2024 = get_state_value(feature_classes_2024[fc], old_objectid_2024, field_b)

            # 标记是否找到匹配的19年记录
            matched_2019 = None

            # 遍历所有19年数据寻找A字段匹配
            for record_2019 in pending_2019_ids:
                if record_2019[field_a] == a_value_2024:
                    matched_2019 = record_2019
                    break  # 找到第一个匹配的19年记录即停止

            # 根据匹配结果设置STATE
            if matched_2019:
                # 找到A字段匹配的19年记录，继续检查B字段
                b_value_2019 = matched_2019[field_b]

                # 使用相对误差计算相似度
                similarity = calculate_relative_error(b_value_2019, b_value_2024, similarity_threshold)

                if similarity == 5:
                    # 任意B值为0，标记为5
                    print("  2024年ID=%d 与 2019年ID=%d B值含0，STATE=5" %
                          (old_objectid_2024, matched_2019["old_objectid"]))
                    state = 5
                elif similarity >= similarity_threshold:
                    # 相似度达标，标记为4（无变化）
                    print("  2024年ID=%d 与 2019年ID=%d A匹配，B相似度=%.2f≥%.1f，STATE=4" %
                          (old_objectid_2024, matched_2019["old_objectid"], similarity, similarity_threshold))
                    state = 4
                else:
                    # 相似度不达标，标记为2（新增）
                    print("  2024年ID=%d 与 2019年ID=%d A匹配，B相似度=%.2f<%.1f，STATE=2" %
                          (old_objectid_2024, matched_2019["old_objectid"], similarity, similarity_threshold))
                    state = 2

                with arcpy.da.UpdateCursor(feature_classes_2024[fc],
                                           ["STATE"],
                                           "OLD_OBJECTID = %d" % old_objectid_2024) as cursor:
                    for row in cursor:
                        row[0] = state
                        cursor.updateRow(row)
            else:
                # 未找到A字段匹配的19年记录，标记为5（新增但无匹配）
                print("  2024年ID=%d 未找到A字段匹配的19年记录，STATE=5" % old_objectid_2024)
                with arcpy.da.UpdateCursor(feature_classes_2024[fc],
                                           ["STATE"],
                                           "OLD_OBJECTID = %d" % old_objectid_2024) as cursor:
                    for row in cursor:
                        row[0] = 5
                        cursor.updateRow(row)

        # 处理2019年数据（按OLD_OBJECTID升序）
        for record_2019 in pending_2019_ids:
            old_objectid_2019 = record_2019["old_objectid"]
            a_value_2019 = record_2019[field_a]
            b_value_2019 = record_2019[field_b]

            # 标记是否找到匹配的24年记录
            matched_2024 = None

            # 遍历所有24年数据寻找A字段匹配
            for old_objectid_2024 in pending_2024_ids:
                a_value_2024 = get_state_value(feature_classes_2024[fc], old_objectid_2024, field_a)
                if a_value_2024 == a_value_2019:
                    matched_2024 = old_objectid_2024
                    break  # 找到第一个匹配的24年记录即停止

            # 根据匹配结果设置STATE
            if matched_2024:
                # 找到A字段匹配的24年记录，继续检查B字段
                b_value_2024 = get_state_value(feature_classes_2024[fc], matched_2024, field_b)

                # 使用相对误差计算相似度
                similarity = calculate_relative_error(b_value_2019, b_value_2024, similarity_threshold)

                if similarity == 5:
                    # 任意B值为0，标记为5
                    print("  2019年ID=%d 与 2024年ID=%d B值含0，STATE=5" %
                          (old_objectid_2019, matched_2024))
                    state = 5
                elif similarity >= similarity_threshold:
                    # 相似度达标，标记为4（无变化）
                    print("  2019年ID=%d 与 2024年ID=%d A匹配，B相似度=%.2f≥%.1f，STATE=4" %
                          (old_objectid_2019, matched_2024, similarity, similarity_threshold))
                    state = 4
                else:
                    # 相似度不达标，标记为3（消失）
                    print("  2019年ID=%d 与 2024年ID=%d A匹配，B相似度=%.2f<%.1f，STATE=3" %
                          (old_objectid_2019, matched_2024, similarity, similarity_threshold))
                    state = 3

                with arcpy.da.UpdateCursor(feature_classes_2019[fc],
                                           ["STATE"],
                                           "OLD_OBJECTID = %d" % old_objectid_2019) as cursor:
                    for row in cursor:
                        row[0] = state
                        cursor.updateRow(row)
            else:
                # 未找到A字段匹配的24年记录，标记为5（消失但无匹配）
                print("  2019年ID=%d 未找到A字段匹配的24年记录，STATE=5" % old_objectid_2019)
                with arcpy.da.UpdateCursor(feature_classes_2019[fc],
                                           ["STATE"],
                                           "OLD_OBJECTID = %d" % old_objectid_2019) as cursor:
                    for row in cursor:
                        row[0] = 5
                        cursor.updateRow(row)

    # ============== 第三次提取：处理STATE=5的数据 ==============
    print("\n===== 开始第三次提取 =====")
    for fc in common_fcs:
        print("\n处理要素类: %s" % fc)

        # 获取2019年所有OLD_OBJECTID
        all_2019_oids = set()
        with arcpy.da.SearchCursor(feature_classes_2019[fc], ["OLD_OBJECTID"]) as cursor:
            for row in cursor:
                all_2019_oids.add(row[0])
        print("  2019年所有OLD_OBJECTID数量: %d" % len(all_2019_oids))

        # 获取2024年所有OLD_OBJECTID
        all_2024_oids = set()
        with arcpy.da.SearchCursor(feature_classes_2024[fc], ["OLD_OBJECTID"]) as cursor:
            for row in cursor:
                all_2024_oids.add(row[0])
        print("  2024年所有OLD_OBJECTID数量: %d" % len(all_2024_oids))

        # 处理2024年中STATE=5的数据
        state5_2024_ids = []
        with arcpy.da.SearchCursor(feature_classes_2024[fc], ["OLD_OBJECTID"], "STATE = 5") as cursor:
            for row in cursor:
                state5_2024_ids.append(row[0])
        print("  2024年STATE=5的记录数量: %d" % len(state5_2024_ids))

        for oid_2024 in state5_2024_ids:
            if oid_2024 not in all_2019_oids:
                # OLD_OBJECTID在19年不存在，标记为2（新增）
                print("  2024年ID=%d 在19年中不存在，STATE从5改为2" % oid_2024)
                with arcpy.da.UpdateCursor(feature_classes_2024[fc], ["STATE"], "OLD_OBJECTID = %d" % oid_2024) as cursor:
                    for row in cursor:
                        row[0] = 2
                        cursor.updateRow(row)
            else:
                # OLD_OBJECTID在19年存在，STATE保持5
                print("  2024年ID=%d 在19年中存在，STATE保持5" % oid_2024)

        # 处理2019年中STATE=5的数据
        state5_2019_ids = []
        with arcpy.da.SearchCursor(feature_classes_2019[fc], ["OLD_OBJECTID"], "STATE = 5") as cursor:
            for row in cursor:
                state5_2019_ids.append(row[0])
        print("  2019年STATE=5的记录数量: %d" % len(state5_2019_ids))

        for oid_2019 in state5_2019_ids:
            if oid_2019 not in all_2024_oids:
                # OLD_OBJECTID在24年不存在，标记为3（消失）
                print("  2019年ID=%d 在24年中不存在，STATE从5改为3" % oid_2019)
                with arcpy.da.UpdateCursor(feature_classes_2019[fc], ["STATE"], "OLD_OBJECTID = %d" % oid_2019) as cursor:
                    for row in cursor:
                        row[0] = 3
                        cursor.updateRow(row)
            else:
                # OLD_OBJECTID在24年存在，STATE保持5
                print("  2019年ID=%d 在24年中存在，STATE保持5" % oid_2019)

    # ============== 输出最终结果 ==============
    print("\n===== 输出最终结果 =====")
    for fc in common_fcs:
        print("\n导出要素类: %s" % fc)

        # 导出2024年STATE=2和STATE=5的数据（新增）
        new_fc_name = "%s_add" % fc
        new_fc_path = os.path.join(add_output_gdb, new_fc_name)
        sql_export_new = "STATE IN (2, 5)"  # 同时导出STATE=2和STATE=5
        arcpy.FeatureClassToFeatureClass_conversion(
            in_features=feature_classes_2024[fc],
            out_path=add_output_gdb,
            out_name=new_fc_name,
            where_clause=sql_export_new
        )
        count_new = int(arcpy.GetCount_management(new_fc_path).getOutput(0))
        print("  导出新增相关记录(STATE=2和STATE=5): %d 条，名称: %s" % (count_new, new_fc_name))

        # 导出2019年STATE=3和STATE=5的数据（消失）
        deleted_fc_name = "%s_no" % fc
        deleted_fc_path = os.path.join(no_output_gdb, deleted_fc_name)
        sql_export_deleted = "STATE IN (3, 5)"  # 同时导出STATE=3和STATE=5
        arcpy.FeatureClassToFeatureClass_conversion(
            in_features=feature_classes_2019[fc],
            out_path=no_output_gdb,
            out_name=deleted_fc_name,
            where_clause=sql_export_deleted
        )
        count_deleted = int(arcpy.GetCount_management(deleted_fc_path).getOutput(0))
        print("  导出消失相关记录(STATE=3和STATE=5): %d 条，名称: %s" % (count_deleted, deleted_fc_name))

except arcpy.ExecuteError:
    print(arcpy.GetMessages(2))
except Exception as e:
    print("发生未知错误: %s" % str(e))